package org.neframework.webtest.util.office.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

/**
 * 操作Excel表格的功能类
 */
public class ExcelReplaceTools {

	/**
	 * 用 map1 替换Excel数据内容 <@#>开头的标记
	 * 
	 * @param map1
	 * 
	 */
	public static void replaceExcelContent(File file, Map<String, Object> map1) {
		POIFSFileSystem fs;
		HSSFSheet sheet;
		HSSFRow row;

		HSSFWorkbook wb = null;
		InputStream is = null;
		try {
			is = new FileInputStream(file);
			fs = new POIFSFileSystem(is);
			wb = new HSSFWorkbook(fs);

			sheet = wb.getSheetAt(0);
			// 得到总行数
			int rowNum = sheet.getLastRowNum();
			System.err.println("总行数-----------" + rowNum);

			row = sheet.getRow(0);
			int colNum = row.getPhysicalNumberOfCells();
			System.err.println("列:" + colNum);
			// 正文内容应该从第二行开始,第一行为表头的标题
			for (int i = 0; i <= rowNum + 1; i++) {
				row = sheet.getRow(i);
				if (row == null) {
					continue;
				}
				int j = 0;
				while (j < colNum) {
					// 每个单元格的数据内容用"-"分割开，以后需要时用String类的replace()方法还原数据
					// 也可以将每个单元格的数据设置到一个javabean的属性中，此时需要新建一个javabean
					// str += getStringCellValue(row.getCell((short) j)).trim()
					// +
					// "-";
					HSSFCell cell = row.getCell((short) j);
					// System.err.println("-->" + cell);
					if (cell != null) {
						// if ("@#name".equals(cell.toString())) {
						// cell.setCellValue("冯晓东");
						// }
						String key = cell.toString();
						if (key.startsWith("@#")) {
							key = key.replace("@#", "");
							Object object = map1.get(key);
							if (object == null) {
								object = "";
							}
							cell.setCellValue(object.toString());
						}

					}

					j++;
				}// #while
			}// #for

		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			if (is != null) {
				try {
					is.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}

			FileOutputStream fos = null;
			try {
				fos = new FileOutputStream(file);
				wb.write(fos);
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				if (fos != null) {
					try {
						fos.close();
					} catch (IOException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}
			}

		}
	}// #readExcelContent

	public static void main(String[] args) {
		// 对读取Excel表格标题测试
		ExcelReplaceTools excelReader = new ExcelReplaceTools();
		// 对读取Excel表格内容测试
		// excelReader.replaceExcelContent("f:\\test.xls");

	}
}